package com.ubadong.studentmng.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ubadong.studentmng.dao.StudentDAO;
import com.ubadong.studentmng.util.JDBCUtil;
import com.ubadong.studentmng.vo.Student;


public class StudentDAOImpl implements StudentDAO{
	
	public int countAllStudent(Student student){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from student where 1=1 ");
		String name = student.getName();
		String edu = student.getEdu();
		int gender = student.getGender();

		//判断用户名是否为空
		if(name != null && name.length()>0){
			sql.append("and name like '%"+name+"%' ");
		}
		//判断学历是否为空
		if(edu != null && !"".equals(edu)){
			sql.append("and edu='"+edu+"' ");
		}
		//判断性别是否选择
		if(gender != -1){
			sql.append("and gender="+gender+" ");
		}
		

		System.out.println(sql);
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();
		//用于存放总记录数
		int counts = 0;
		try {
			//取得数据库连接
			conn = JDBCUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			if(rs.next()){
				counts = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			JDBCUtil.close(rs, st, conn);
		}
		
		return counts;
	}

	public List<Student> getAllStudent(Student student) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from student where 1=1 ");
		String name = student.getName();
		String edu = student.getEdu();
		int gender = student.getGender();

		//判断用户名是否为空
		if(name != null && name.length()>0){
			sql.append("and name like '%"+name+"%' ");
		}
		//判断学历是否为空
		if(edu != null && !"".equals(edu)){
			sql.append("and edu='"+edu+"' ");
		}
		//判断性别是否选择
		if(gender != -1){
			sql.append("and gender="+gender);
		}
		//分页信息
		sql.append("limit "+student.getOffset()+" ,"+student.getPageSize());
		
		
		System.out.println(sql);
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();
		try {
			//取得数据库连接
			conn = JDBCUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			//获取并封装数据
			while(rs.next()){
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				s.setPasswd(rs.getString("passwd"));
				s.setAge(rs.getInt("age"));
				s.setGender(rs.getInt("gender"));
				s.setHobby(rs.getString("hobby"));
				s.setBirthday(rs.getString("birthday"));
				s.setEdu(rs.getString("edu"));
				s.setRemark(rs.getString("remark"));
				//将数据封装到list集合
				list.add(s);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			JDBCUtil.close(rs, st, conn);
		}
		
		return list;
	}
	
	//增加学生
	public void addStduent(Student student) {
		
		StringBuffer sql = new StringBuffer();
		sql.append("insert into student(name,passwd,age,gender,hobby,birthday,edu,remark) values(");
		sql.append("'"+student.getName()+"',");
		sql.append("'"+student.getPasswd()+"',");
		sql.append(student.getAge()+",");
		sql.append(student.getGender()+",");
		sql.append("'"+student.getHobby()+"',");
		sql.append("'"+student.getBirthday()+"',");
		sql.append("'"+student.getEdu()+"',");
		sql.append("'"+student.getRemark()+"')");

		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();
			st = conn.createStatement();
			st.executeUpdate(sql.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			JDBCUtil.close(rs, st, conn);
		}

	
	}

	//查询单个用户
	public Student getStudentById(int id){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from student where id= "+id);

		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			//取得数据库连接
			conn = JDBCUtil.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			//获取并封装数据
			if(rs.next()){
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				s.setPasswd(rs.getString("passwd"));
				s.setAge(rs.getInt("age"));
				s.setGender(rs.getInt("gender"));
				s.setHobby(rs.getString("hobby"));
				s.setBirthday(rs.getString("birthday"));
				s.setEdu(rs.getString("edu"));
				s.setRemark(rs.getString("remark"));
				return s;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			JDBCUtil.close(rs, st, conn);
		}
		
		return null;
	}
	
	//更新学生
	public void updateStduent(Student student) {
		StringBuffer sql = new StringBuffer();
		sql.append("update student set name=?,passwd=?,age=?,gender=?,hobby=?,birthday=?,edu=?,remark=? where id=?");

		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			//取得数据库连接
			conn = JDBCUtil.getConnection();			
			st = conn.prepareStatement(sql.toString());
			st.setString(1, student.getName());
			st.setString(2, student.getPasswd());
			st.setInt(3, student.getAge());
			st.setInt(4, student.getGender());
			st.setString(5, student.getHobby());
			st.setString(6, student.getBirthday());
			st.setString(7, student.getEdu());
			st.setString(8, student.getRemark());
			st.setInt(9, student.getId());
			st.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			JDBCUtil.close(rs, st, conn);
		}
	}


	public void deleteStduent(String ids) {
		StringBuffer sql = new StringBuffer();
		sql.append("delete from student where id in ("+ids+")");
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			//取得数据库连接
			conn = JDBCUtil.getConnection();
			st = conn.createStatement();
			st.executeUpdate(sql.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			JDBCUtil.close(rs, st, conn);
		}
	}

}
